/*******************************************************************************
 * Copyright (c) 2005, 2014 springside.github.io
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package com.sdgakj.repository;

import com.sdgakj.entity.Family;
import com.sdgakj.entity.FamilyPerson;
import com.sdgakj.entity.Person;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

public interface FamilyPersonDao extends PagingAndSortingRepository<FamilyPerson, Long>,JpaSpecificationExecutor<FamilyPerson> {

    /**
     * 根据用户id获取家族
     *
     * @param id
     * @return
     */
    List<FamilyPerson> getFamilyByFpId(Long id);

    @Query(value = "select * from f_family_person where ff_id=?1 and state='1'", nativeQuery = true)
    List<FamilyPerson> getPersonByFfId(Long id);
    @Query(value = "select * from f_family_person where ff_id=?1 and state='0'", nativeQuery = true)
    List<FamilyPerson> getPersonByFfIdAndState(Long id);
    /*
    * 通过家族id 和 人的id查询
    *
    * */
    @Query(value = "select * from f_family_person where ff_id=?1 and fp_id=?2", nativeQuery = true)
    FamilyPerson getPersonByFIdAndPId(Long famiylyId, Long personId);


    /**
     * 根据 家族id寻找家族人数总和
     *
     * @param
     * @return
     */
    @Query(value = "SELECT COUNT(1) FROM f_family_person WHERE ff_id = ?1 AND generation = ?2", nativeQuery = true)
    Integer getPersonByFfIdangeneration(Long fpid, Long genumber);

    /**
     * @return
     */
    @Query(value = "select * from f_family_person where ff_id=?1 group by generation", nativeQuery = true)
    List<FamilyPerson> getPersongenerationByfid(Long fid);

    @Query(value = "select * from f_family_person where ff_id=?1", nativeQuery = true)
    List<FamilyPerson> getCountByfid(Long fid);

    //查询 世系
    @Query(value = "select generation from f_family_person where ff_id= ?1 group by generation", nativeQuery = true)
    List<Integer> getPersonGeneration(Long family);
}